﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using NPOI.SS.Formula.Functions;
using IntelligentEscalator.BUS;
using IntelligentEscalator.Models;

namespace IntelligentEscalator.DAL
{
    public class InspectionPlanDAL
    {
        public int QuerTodayPlanCount()
        {
            string strSql = "SELECT COUNT(1) FROM inspection_plan where to_days(preparationTime) = to_days(now())";
            int count = SqlHelper<InspectionPlanInfo>.SqlScalar(strSql);

            return count;
        }

        public string AddPlan(InspectionPlanInfo planinfo)
        {
            string strSql = string.Format("INSERT INTO inspection_plan(elevatorId,inspectionNo,inspectionDatetime,inspectionGroupid,preparationTime,isDelete,inspectionStateId,workattribute ,receiveTime,arriveTime,completeTime,acceptanceTime)VALUES({0},'{1}',{2},{3},{4},0,{5},3,{6},{7},{8},{9})",
                planinfo.elevatorId, planinfo.inspectionNo, planinfo.inspectionDatetime, planinfo.inspectionGroupid, planinfo.preParationTime, planinfo.inspectionStateId, planinfo.receiveTime, planinfo.arriveTime, planinfo.completeTime, planinfo.acceptanceTime);

            try
            {
                int count = SqlHelper<UserInfo>.SqlExecuteRows(strSql);

                if (count > 0)
                {
                    return "success";
                }
                return "fail";
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }

        public string EditPlan(InspectionPlanInfo planinfo)
        {
            string strSql = string.Format("UPDATE inspection_plan SET inspectionStateId={0} ,preParationTime={1},receiveTime={2},arriveTime={3},completeTime={4},acceptanceTime={5} WHERE id={6}",
                planinfo.inspectionStateId, planinfo.preParationTime, planinfo.receiveTime, planinfo.arriveTime, planinfo.completeTime, planinfo.acceptanceTime, planinfo.id);

            try
            {
                if (SqlHelper<MaintainInfo>.SqlExecute(strSql))
                {
                    return "success";
                }
                else
                {
                    return "fail";
                }
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }

        public InspectionPlanInfo LoadPlan(int planid)
        {
            string strSql = "SELECT vi_inspectioninfo.*,area.areaName AS mainAreaName FROM vi_inspectioninfo LEFT JOIN area ON vi_inspectioninfo.mainAreaId=area.id WHERE vi_inspectioninfo.id=" + planid;
            using (var conn = DataFactory.GetSQLConnection())
            {
                CommFN<InspectionPlanInfo> commFN = new CommFN<InspectionPlanInfo>();
                return commFN.GetData(strSql);
            }
        }

        public List<UserInfo> GetRepairperson(string OrderNo, int OrderType)
        {
            string strSql = "SELECT b.fullname FROM workorder_repairperson a LEFT JOIN `user` b ON a.repairUserId=b.id WHERE a.orderNo='" + OrderNo + "' AND a.workOrderType=" + OrderType;
            return SqlHelper<UserInfo>.SqlList(strSql);
        }

        public List<InspectionPlanInfo> GetOverDueInspection()
        {
            string strSql = string.Format("SELECT id FROM inspection_plan WHERE isDelete=0 AND TIMESTAMPDIFF(HOUR,DATE_FORMAT(inspectionDatetime,'%Y-%m-%d %T'),DATE_FORMAT(NOW(), '%Y-%m-%d %T'))>= 24 AND inspectionStateId!=3 AND inspectionStateId!=4 AND workattribute!=0");
            return SqlHelper<InspectionPlanInfo>.SqlList(strSql);
        }

        public List<InspectionPlanInfo> QueryPlan(InspectionPlanInfo planinfo)
        {
            try
            {
                List<InspectionPlanInfo> list = new List<InspectionPlanInfo>();
                string strSql = "SELECT vi_inspectioninfo.*,area.areaName AS mainAreaName FROM vi_inspectioninfo LEFT JOIN area ON vi_inspectioninfo.mainAreaId=area.id WHERE vi_inspectioninfo.inspectionStateId!=4";
                if (planinfo.elevatorId > 0)
                {
                    strSql += " AND vi_inspectioninfo.elevatorId=@elevatorId";
                }
                if (!string.IsNullOrEmpty(planinfo.elevatorName))
                {
                    strSql += " AND vi_inspectioninfo.elevatorName LIKE @elevatorName";
                }
                if (!string.IsNullOrEmpty(planinfo.elevatorNo))
                {
                    strSql += " AND vi_inspectioninfo.elevatorNo LIKE @elevatorNo";
                }
                if (!string.IsNullOrEmpty(planinfo.startTime))
                {
                    strSql += " AND DATE(vi_inspectioninfo.inspectionDatetime)>=DATE(@startTime)";
                }
                if (!string.IsNullOrEmpty(planinfo.endTime))
                {
                    strSql += " AND DATE(vi_inspectioninfo.inspectionDatetime)<=DATE(@endTime)";
                }
                strSql += " ORDER BY vi_inspectioninfo.inspectionDatetime DESC";
                if (planinfo.Recordhead > -1 && planinfo.Recordtail > 0)
                {
                    strSql += " LIMIT @Recordhead,@Recordtail";
                }

                using (MySqlConnection connection = DataFactory.GetMysqlConnection())
                {
                    MySqlCommand mySqlCommand = new MySqlCommand(strSql, connection);

                    // 添加参数
                    string likeno = "%" + planinfo.elevatorName + "%";
                    string likename = "%" + planinfo.elevatorNo + "%";
                    mySqlCommand.Parameters.AddWithValue("@elevatorNO", $"%{likeno}%");
                    mySqlCommand.Parameters.AddWithValue("@elevatorName", $"%{likename}%");
                    mySqlCommand.Parameters.AddWithValue("@elevatorId", planinfo.elevatorId);
                    mySqlCommand.Parameters.AddWithValue("@startTime", Convert.ToDateTime(planinfo.startTime).ToString("yyyy-MM-dd HH:mm:ss"));
                    mySqlCommand.Parameters.AddWithValue("@endTime", Convert.ToDateTime(planinfo.endTime).ToString("yyyy-MM-dd HH:mm:ss"));
                    mySqlCommand.Parameters.AddWithValue("@Recordhead", planinfo.Recordhead);
                    mySqlCommand.Parameters.AddWithValue("@Recordtail",planinfo.Recordtail);
                    MySqlDataAdapter adapter = new MySqlDataAdapter(mySqlCommand);
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet);
                    DataTable dataTable = dataSet.Tables[0];
                    list = ToList<InspectionPlanInfo>(dataTable);
                }
                return list;
            }
            catch (Exception ex)
            {
                return null;
            }

        }

        public int QueryPlanCount(InspectionPlanInfo planinfo)
        {
            int count = 0;
            try
            {
                string strSql = "SELECT COUNT(1) FROM vi_inspectioninfo WHERE inspectionStateId!=4";
                if (planinfo.elevatorId > 0)
                {
                    strSql += " AND elevatorId=@elevatorId";
                }
                if (!string.IsNullOrEmpty(planinfo.elevatorName))
                {
                    strSql += " AND elevatorName LIKE @elevatorName";
                }
                if (!string.IsNullOrEmpty(planinfo.elevatorNo))
                {
                    strSql += " AND elevatorNo LIKE @elevatorNo";
                }
                if (!string.IsNullOrEmpty(planinfo.startTime))
                {
                    strSql += " AND DATE(inspectionDatetime)>=DATE(@startTime)";
                }
                if (!string.IsNullOrEmpty(planinfo.endTime))
                {
                    strSql += " AND DATE(inspectionDatetime)<=DATE(@endTime)";
                }

                using (MySqlConnection connection = DataFactory.GetMysqlConnection())
                {
                    MySqlCommand mySqlCommand = new MySqlCommand(strSql, connection);

                    // 添加参数
                    string likeno = "%" + planinfo.elevatorName + "%";
                    string likename = "%" + planinfo.elevatorNo + "%";
                    mySqlCommand.Parameters.AddWithValue("@elevatorNO", $"%{likeno}%");
                    mySqlCommand.Parameters.AddWithValue("@elevatorName", $"%{likename}%");
                    mySqlCommand.Parameters.AddWithValue("@elevatorId", planinfo.elevatorId);
                    mySqlCommand.Parameters.AddWithValue("@startTime", Convert.ToDateTime(planinfo.startTime).ToString("yyyy-MM-dd HH:mm:ss"));
                    mySqlCommand.Parameters.AddWithValue("@endTime", Convert.ToDateTime(planinfo.endTime).ToString("yyyy-MM-dd HH:mm:ss"));
                    MySqlDataAdapter adapter = new MySqlDataAdapter(mySqlCommand);
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet);
                    count = Convert.ToInt32(dataSet.Tables[0].Rows[0][0]);
                }
            }
            catch (Exception ex)
            {

            }
            return count;
        }

        public List<InspectionPlanInfo> QueryInspectionHistory(InspectionPlanInfo planinfo)
        {
            try
            {
                List<InspectionPlanInfo> list = new List<InspectionPlanInfo>();
                string strSql = "SELECT vi_inspectioninfo.*,area.areaName AS mainAreaName FROM vi_inspectioninfo LEFT JOIN area ON vi_inspectioninfo.mainAreaId=area.id WHERE vi_inspectioninfo.inspectionStateId=4";
                if (planinfo.elevatorId > 0)
                {
                    strSql += " AND vi_inspectioninfo.elevatorId=@elevatorId";
                }
                if (!string.IsNullOrEmpty(planinfo.elevatorName))
                {
                    strSql += " AND vi_inspectioninfo.elevatorName LIKE @elevatorName";
                }
                if (!string.IsNullOrEmpty(planinfo.elevatorNo))
                {
                    strSql += " AND vi_inspectioninfo.elevatorNo LIKE @elevatorNo";
                }
                if (!string.IsNullOrEmpty(planinfo.startTime))
                {
                    strSql += " AND DATE(vi_inspectioninfo.inspectionDatetime)>=DATE(@startTime)";
                }
                if (!string.IsNullOrEmpty(planinfo.endTime))
                {
                    strSql += " AND DATE(vi_inspectioninfo.inspectionDatetime)<=DATE(@endTime)";
                }

                strSql += " ORDER BY vi_inspectioninfo.inspectionDatetime DESC";
                if (planinfo.Recordhead > -1 && planinfo.Recordtail > 0)
                {
                    strSql += " LIMIT @Recordhead,@Recordtail";
                }


                using (MySqlConnection connection = DataFactory.GetMysqlConnection())
                {
                    MySqlCommand mySqlCommand = new MySqlCommand(strSql, connection);

                    // 添加参数
                    // 添加参数
                    string likeno = "%" + planinfo.elevatorName + "%";
                    string likename = "%" + planinfo.elevatorNo + "%";
                    mySqlCommand.Parameters.AddWithValue("@elevatorNO", $"%{likeno}%");
                    mySqlCommand.Parameters.AddWithValue("@elevatorName", $"%{likename}%");
                    mySqlCommand.Parameters.AddWithValue("@elevatorId", planinfo.elevatorId);
                    mySqlCommand.Parameters.AddWithValue("@startTime", Convert.ToDateTime(planinfo.startTime).ToString("yyyy-MM-dd HH:mm:ss"));
                    mySqlCommand.Parameters.AddWithValue("@endTime", Convert.ToDateTime(planinfo.endTime).ToString("yyyy-MM-dd HH:mm:ss"));
                    mySqlCommand.Parameters.AddWithValue("@Recordhead", planinfo.Recordhead);
                    mySqlCommand.Parameters.AddWithValue("@Recordtail", planinfo.Recordtail);
                    MySqlDataAdapter adapter = new MySqlDataAdapter(mySqlCommand);
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet);
                    DataTable dataTable = dataSet.Tables[0];
                    list = ToList<InspectionPlanInfo>(dataTable);
                }

                return list;
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        public List<InspectionPlanInfo> QueryInspectionHistoryByIds(InspectionPlanInfo planinfo)
        {
            string strSql = "SELECT vi_inspectioninfo.*,area.areaName AS mainAreaName FROM vi_inspectioninfo LEFT JOIN area ON vi_inspectioninfo.mainAreaId=area.id WHERE vi_inspectioninfo.id in (" + planinfo.ids + ")";
            return SqlHelper<InspectionPlanInfo>.SqlList(strSql);
        }

        public int QueryInspectionHistoryCount(InspectionPlanInfo planinfo)
        {
            int count = 0;
            try
            {
                string strSql = "SELECT COUNT(1) FROM vi_inspectioninfo WHERE inspectionStateId=4";
                if (planinfo.elevatorId > 0)
                {
                    strSql += " AND vi_inspectioninfo.elevatorId=@elevatorId";
                }
                if (!string.IsNullOrEmpty(planinfo.elevatorName))
                {
                    strSql += " AND vi_inspectioninfo.elevatorName LIKE @elevatorName";
                }
                if (!string.IsNullOrEmpty(planinfo.elevatorNo))
                {
                    strSql += " AND vi_inspectioninfo.elevatorNo LIKE @elevatorNo";
                }
                if (!string.IsNullOrEmpty(planinfo.startTime))
                {
                    strSql += " AND DATE(vi_inspectioninfo.inspectionDatetime)>=DATE(@startTime)";
                }
                if (!string.IsNullOrEmpty(planinfo.endTime))
                {
                    strSql += " AND DATE(vi_inspectioninfo.inspectionDatetime)<=DATE(@endTime)";
                }
                using (MySqlConnection connection = DataFactory.GetMysqlConnection())
                {
                    MySqlCommand mySqlCommand = new MySqlCommand(strSql, connection);

                    // 添加参数
                    string likeno = "%" + planinfo.elevatorName + "%";
                    string likename = "%" + planinfo.elevatorNo + "%";
                    mySqlCommand.Parameters.AddWithValue("@elevatorNO", $"%{likeno}%");
                    mySqlCommand.Parameters.AddWithValue("@elevatorName", $"%{likename}%");
                    mySqlCommand.Parameters.AddWithValue("@elevatorId", planinfo.elevatorId);
                    mySqlCommand.Parameters.AddWithValue("@startTime", Convert.ToDateTime(planinfo.startTime).ToString("yyyy-MM-dd HH:mm:ss"));
                    mySqlCommand.Parameters.AddWithValue("@endTime", Convert.ToDateTime(planinfo.endTime).ToString("yyyy-MM-dd HH:mm:ss"));
                    MySqlDataAdapter adapter = new MySqlDataAdapter(mySqlCommand);
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet);
                    count = Convert.ToInt32(dataSet.Tables[0].Rows[0][0]);
                }
            }
            catch (Exception ex)
            {

            }
            return count;
        }


        public string UpdateInspectionTime(InspectionPlanInfo planinfo)
        {
            string strSql = "UPDATE inspection_plan SET";
            string sqlwhere = string.Empty;
            if (!string.IsNullOrEmpty(planinfo.receiveTime))
            {
                sqlwhere += " receiveTime = '" + planinfo.receiveTime + "',";
            }
            if (!string.IsNullOrEmpty(planinfo.arriveTime))
            {
                sqlwhere += " arriveTime = '" + planinfo.arriveTime + "',";
            }
            if (!string.IsNullOrEmpty(planinfo.completeTime))
            {
                sqlwhere += " completeTime = '" + planinfo.completeTime + "',";
            }
            if (sqlwhere.Length > 0)
            {
                strSql += sqlwhere.Substring(0, sqlwhere.Length - 1) + " where id=" + planinfo.id;
            }

            try
            {
                if (SqlHelper<InspectionPlanInfo>.SqlExecute(strSql))
                {
                    return "success";
                }
                else
                {
                    return "fail";
                }
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }

        public string AcceptanceInspection(InspectionPlanInfo planinfo)
        {
            string strSql = "UPDATE inspection_plan SET inspectionStateId=4,acceptanceTime='" + DateTime.Now.ToString() + "',workattribute=3 WHERE id=" + planinfo.id;
            using (var conn = DataFactory.GetSQLConnection())
            {
                try
                {
                    int count = SqlHelper<InspectionPlanInfo>.SqlExecuteRows(strSql);

                    if (count > 0)
                    {
                        return "success";
                    }
                    return "fail";
                }
                catch (Exception e)
                {
                    return "fail," + e.ToString();
                }
            }
        }

        public List<WorkorderState> GetWorkOrderStateList()
        {
            string strSql = "SELECT * FROM workorderstate";

            return SqlHelper<WorkorderState>.SqlList(strSql);
        }

        public string DelPlan(int planid)
        {
            string strSql = "delete FROM inspection_plan WHERE id=" + planid;
            try
            {
                int count = SqlHelper<InspectionPlanInfo>.SqlExecuteRows(strSql);

                if (count > 0)
                {
                    return "success";
                }
                return "fail";
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }

        public string CheckWorkNo(string workno)
        {
            string strsql = "select inspectionNo FROM inspection_plan WHERE inspectionNo='" + workno + "'";
            return SqlHelper<InspectionPlanInfo>.SqlScalarString(strsql);
        }

        public string BatchDelPlan(string ids)
        {
            string strSql = "delete FROM inspection_plan WHERE id in(" + ids + ")";
            try
            {
                int count = SqlHelper<InspectionPlanInfo>.SqlExecuteRows(strSql);

                if (count > 0)
                {
                    return "success";
                }
                return "fail";
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }

        public List<T> ToList<T>(DataTable dt) where T : class, new()
        {
            List<T> list = new List<T>();
            //确认参数有效,若无效则返回Null
            if (dt == null)
                return list;
            else if (dt.Rows.Count == 0)
                return list;
            Dictionary<string, string> dicField = new Dictionary<string, string>();
            Dictionary<string, string> dicProperty = new Dictionary<string, string>();
            Type type = typeof(T);

            //创建字段字典，方便查找字段名
            foreach (var item in type.GetFields())
            {
                dicField.Add(item.Name.ToLower(), item.Name);
            }
            //创建属性字典，方便查找属性名
            foreach (var item in type.GetProperties())
            {
                dicProperty.Add(item.Name.ToLower(), item.Name);
            }

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                //创建泛型对象
                T _t = Activator.CreateInstance<T>();
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    string memberKey = dt.Columns[j].ColumnName.ToLower();

                    //字段赋值
                    if (dicField.ContainsKey(memberKey))
                    {
                        FieldInfo theField = type.GetField(dicField[memberKey]);
                        var dbValue = dt.Rows[i][j];
                        if (dbValue.GetType() == typeof(DBNull))
                            dbValue = null;
                        if (dbValue != null)
                        {
                            Type memberType = theField.FieldType;
                            //dbValue = dbValue.ChangeType(memberType);
                            dbValue = Convert.ChangeType(dbValue, memberType);
                        }
                        theField.SetValue(_t, dbValue);
                    }
                    //属性赋值
                    if (dicProperty.ContainsKey(memberKey))
                    {
                        PropertyInfo theProperty = type.GetProperty(dicProperty[memberKey]);
                        if (!theProperty.CanWrite) continue;
                        var dbValue = dt.Rows[i][j];
                        if (dbValue.GetType() == typeof(DBNull))
                            dbValue = null;
                        if (dbValue != null)
                        {
                            Type memberType = theProperty.PropertyType;
                            if (memberType.IsGenericType && memberType.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))
                            {
                                NullableConverter newNullableConverter = new NullableConverter(memberType);
                                dbValue = newNullableConverter.ConvertFrom(dbValue);
                            }
                            else
                            {
                                dbValue = Convert.ChangeType(dbValue, memberType);
                            }
                        }
                        theProperty.SetValue(_t, dbValue, null);
                    }
                }
                list.Add(_t);
            }
            return list;
        }
    }
}
